package com.ng.common.servlet.service.impl;

import java.util.List;
import java.util.Map;
import java.util.ArrayList;
import java.util.HashMap;
import jakarta.servlet.http.HttpServletRequest;
import jakarta.servlet.http.HttpServletResponse;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONObject;
import com.alibaba.fastjson.TypeReference;
import com.jfinal.kit.StrKit;
import com.jfinal.plugin.activerecord.Db;
import com.ng.biz.model.domain.*;
import com.ng.biz.service.IDatasource;
import com.ng.common.model.dto.ParsedSql;
import com.ng.common.model.response.R;
import com.ng.common.servlet.service.IDbapiServlet;
import com.ng.common.util.RequestParamUtil;
import com.ng.common.util.WebUtil;
import com.ng.common.util.JwtUtil;
import com.ng.common.util.ParsedSqlUtil;
import com.influxdb.client.InfluxDBClient;
import com.influxdb.client.QueryApi;
import com.influxdb.query.FluxRecord;
import com.influxdb.query.FluxTable;

import lombok.extern.slf4j.Slf4j;

@Service
@Slf4j
public class DbapiServletImpl implements IDbapiServlet {

	@Autowired
	private JwtUtil jwtUtil;

	@Autowired
	private IDatasource datasourceImpl;

	@Override
	public void handleRequest(HttpServletRequest request, HttpServletResponse response) {
		String uri = request.getRequestURI().replaceFirst(request.getServletPath(), "");

		Dbapi dbapi = Dbapi.dao.findFirst("select * from " + Dbapi.TABLE_NAME + " where uri=?", uri);
		if (dbapi == null) {
			WebUtil.renderJson(response, R.failed("接口未定义."));
			return;
		}

		if (StrKit.equals("1", dbapi.getIsToken())) {
			if (!validateToken(request)) {
				WebUtil.renderJson(response, R.unauthorized());
				return;
			}
		}

		List<Sql> sqls = Sql.dao
				.find("select * from " + Sql.TABLE_NAME + " a, "+DbapiSql.TABLE_NAME+" b where a.id=b.sql_id and b.api_id=? order by b.sort asc ", dbapi.getId());
		if (sqls == null || sqls.isEmpty()) {
			WebUtil.renderJson(response, R.failed("请先配置接口Sql."));
			return;
		}

		JSONObject jsonObject = new JSONObject();
		jsonObject.put("code", 200);
		jsonObject.put("msg", "成功.");

		JSONObject inputArguments = RequestParamUtil.getRequestParams(request);
		JSONObject data = new JSONObject();
		for (Sql sql : sqls) {
			try {
				executeSQL(inputArguments, sql, data);
			} catch (Exception e) {
				log.error(uri);
				jsonObject.put("code", 500);
				jsonObject.put("msg", e.getMessage());
				break;
			}
		}

		if (jsonObject.getInteger("code") == 200) {
			if (!data.isEmpty()) {
				WebUtil.renderJson(response, R.ok(data));
			} else {
				WebUtil.renderJson(response, R.ok());
			}
		} else {
			WebUtil.renderJson(response, R.failed(jsonObject.getString("msg")));
		}
	}

	/**
	 * 验证Token是否有效
	 * 
	 * @param request
	 * @return
	 */
	private boolean validateToken(HttpServletRequest request) {
		String authorizationHeader = request.getHeader("Authorization");
		if (authorizationHeader != null && authorizationHeader.startsWith("Bearer ")) {
			try {
				String token = authorizationHeader.replaceAll("Bearer ", "");
				String username = jwtUtil.extractUsername(token);
				if (username != null && jwtUtil.validateToken(token, username)) {
					return true;
				}
			}catch (Exception e) {
				log.error("验证Token异常:{}",e.getMessage());
			}
		}
		return false;
	}

	private void executeSQL(JSONObject inputArguments, Sql sql, JSONObject jsonObject) throws Exception {
		JSONObject contentJSONObject = JSONObject.parseObject(sql.getContent());
		if(contentJSONObject == null || contentJSONObject.isEmpty()) {
			throw new Exception(String.format("%s配置有误.", sql.getName()));
		}

		// 处理 InfluxDB 类型
		if(StrKit.equals("InfluxDB", sql.getType())) {
			ParsedSql parsedSql = ParsedSqlUtil.parseInfluxDBFlux(
				contentJSONObject.getString("sql"),
				JSON.parseObject(JSONObject.toJSONString(contentJSONObject.getJSONArray("params")),
				new TypeReference<List<JSONObject>>() {}),
				inputArguments
			);

			if(parsedSql == null) {
				throw new Exception("解析InfluxDB查询语句失败");
			}

			// 获取 InfluxDB 客户端
			InfluxDBClient influxDBClient = datasourceImpl.getInfluxDBClient(sql.getDatasourceId());
			try {
				String query = parsedSql.getSql();
				QueryApi queryApi = influxDBClient.getQueryApi();

				// 根据执行类型执行不同的查询
				switch(sql.getExec()) {
					case "query":
						// 执行 Flux 查询并获取结果
						List<FluxTable> tables = queryApi.query(query);
						List<Map<String, Object>> records = new ArrayList<>();
						
						for (FluxTable table : tables) {
							for (FluxRecord record : table.getRecords()) {
								Map<String, Object> row = new HashMap<>();
								record.getValues().forEach((k, v) -> row.put(k, v));
								records.add(row);
							}
						}
						jsonObject.put(sql.getIdentifier(), records);
						break;
						
					case "queryRaw":
						// 执行原始查询
						String rawResult = queryApi.queryRaw(query);
						jsonObject.put(sql.getIdentifier(), rawResult);
						break;
						
					case "queryFirst":
						// 获取第一条记录
						List<FluxTable> firstTable = queryApi.query(query);
						if (!firstTable.isEmpty() && !firstTable.get(0).getRecords().isEmpty()) {
							FluxRecord firstRecord = firstTable.get(0).getRecords().get(0);
							Map<String, Object> firstRow = new HashMap<>();
							firstRecord.getValues().forEach((k, v) -> firstRow.put(k, v));
							jsonObject.put(sql.getIdentifier(), firstRow);
						} else {
							jsonObject.put(sql.getIdentifier(), null);
						}
						break;
						
					default:
						throw new Exception("不支持的InfluxDB执行类型: " + sql.getExec());
				}
			} finally {
			}
			return;
		}
		
		
		if (StrKit.equals("save", sql.getExec())) {
			com.jfinal.plugin.activerecord.Record record = ParsedSqlUtil.parseSqlToRecord(contentJSONObject.getString("sql"), JSON.parseObject(JSONObject.toJSONString(contentJSONObject.getJSONArray("params")),
					new TypeReference<List<JSONObject>>() {}), inputArguments);
			if(record==null) {
				throw new Exception("保存失败，请检查参数是否正确: " + sql.getExec());
			}
			jsonObject.put(sql.getIdentifier(),Db.use(sql.getDatasourceId()).save(sql.getTable(), sql.getPrimaryKey(), record));
			return;
		}
		
		if(StrKit.equals("update", sql.getExec())) {
			com.jfinal.plugin.activerecord.Record record = ParsedSqlUtil.parseSqlToRecord(contentJSONObject.getString("sql"), JSON.parseObject(JSONObject.toJSONString(contentJSONObject.getJSONArray("params")),
					new TypeReference<List<JSONObject>>() {}), inputArguments);
			if(record==null) {
				throw new Exception("更新失败，请检查参数是否正确: " + sql.getExec());
			}
			jsonObject.put(sql.getIdentifier(),Db.use(sql.getDatasourceId()).update(sql.getTable(), sql.getPrimaryKey(), record));
			return;
		}
		
		// 处理其他类型的数据源
		ParsedSql parsedSql = ParsedSqlUtil.parseSql(
			contentJSONObject.getString("sql"),
			JSON.parseObject(JSONObject.toJSONString(contentJSONObject.getJSONArray("params")),
			new TypeReference<List<JSONObject>>() {}),
			inputArguments
		);

		if(parsedSql == null) {
			throw new Exception("解析sql失败.");
		}
		
		if (StrKit.equals("find", sql.getExec())) {
			jsonObject.put(sql.getIdentifier(), Db.use(sql.getDatasourceId()).find(parsedSql.getSql(),parsedSql.getParams().toArray()));
    	} 
		else if (StrKit.equals("findFirst", sql.getExec())) {
			jsonObject.put(sql.getIdentifier(), Db.use(sql.getDatasourceId()).findFirst(parsedSql.getSql(),parsedSql.getParams().toArray()));
		} 
		else if (StrKit.equals("delete", sql.getExec())) {
			jsonObject.put(sql.getIdentifier(), Db.use(sql.getDatasourceId()).delete(parsedSql.getSql(),parsedSql.getParams().toArray()));
		} 
		else if (StrKit.equals("queryBigDecimal", sql.getExec())) {
			jsonObject.put(sql.getIdentifier(), Db.use(sql.getDatasourceId()).queryBigDecimal(parsedSql.getSql(),parsedSql.getParams().toArray()));
		} 
		else if (StrKit.equals("queryBoolean", sql.getExec())) {
			jsonObject.put(sql.getIdentifier(), Db.use(sql.getDatasourceId()).queryBoolean(parsedSql.getSql(),parsedSql.getParams().toArray()));
		} 
		else if (StrKit.equals("queryByte", sql.getExec())) {
			jsonObject.put(sql.getIdentifier(), Db.use(sql.getDatasourceId()).queryByte(parsedSql.getSql(),parsedSql.getParams().toArray()));
		} 
		else if (StrKit.equals("queryDate", sql.getExec())) {
			jsonObject.put(sql.getIdentifier(), Db.use(sql.getDatasourceId()).queryDate(parsedSql.getSql(),parsedSql.getParams().toArray()));
		} 
		else if (StrKit.equals("queryDouble", sql.getExec())) {
			jsonObject.put(sql.getIdentifier(), Db.use(sql.getDatasourceId()).queryDouble(parsedSql.getSql(),parsedSql.getParams().toArray()));
		} 
		else if (StrKit.equals("queryFloat", sql.getExec())) {
			jsonObject.put(sql.getIdentifier(), Db.use(sql.getDatasourceId()).queryFloat(parsedSql.getSql(),parsedSql.getParams().toArray()));
		}
		else if (StrKit.equals("queryInt", sql.getExec())) {
			jsonObject.put(sql.getIdentifier(), Db.use(sql.getDatasourceId()).queryInt(parsedSql.getSql(),parsedSql.getParams().toArray()));
		}
		else if (StrKit.equals("queryLong", sql.getExec())) {
			jsonObject.put(sql.getIdentifier(), Db.use(sql.getDatasourceId()).queryLong(parsedSql.getSql(),parsedSql.getParams().toArray()));
		}
		else if (StrKit.equals("queryNumber", sql.getExec())) {
			jsonObject.put(sql.getIdentifier(), Db.use(sql.getDatasourceId()).queryNumber(parsedSql.getSql(),parsedSql.getParams().toArray()));
		}
		else if (StrKit.equals("queryShort", sql.getExec())) {
			jsonObject.put(sql.getIdentifier(), Db.use(sql.getDatasourceId()).queryShort(parsedSql.getSql(),parsedSql.getParams().toArray()));
		}
		else if (StrKit.equals("queryStr", sql.getExec())) {
			jsonObject.put(sql.getIdentifier(), Db.use(sql.getDatasourceId()).queryStr(parsedSql.getSql(),parsedSql.getParams().toArray()));
		}
		else if (StrKit.equals("batch", sql.getExec())) {
			throw new Exception(String.format("不支持执行类型:{}.", sql.getExec()));
		} else {
			throw new Exception(String.format("未识别的执行类型:{}.", sql.getExec()));
		}
	}

	/**
	 * 根据传入的 SQL 和参数动态生成最终 SQL
	 *
	 * @param sql    原始 SQL
	 * @param params 查询参数
	 * @return 最终 SQL
	 */
	String getSql(String sql, Map<String, Object> params) {
		// 替换 SQL 中的占位符
		for (Map.Entry<String, Object> entry : params.entrySet()) {
			String key = entry.getKey();
			Object value = entry.getValue();
			if (value != null) {
				sql = sql.replace("{" + key + "}", formatValue(value));
			} else {
				// 如果 value 为 null，移除 SQL 中的条件
				sql = sql.replace(" AND " + key + " = " + formatValue(value), "");
				sql = sql.replace(" AND " + key + " LIKE '%" + value + "%'", "");
				sql = sql.replace(" AND " + key + " != " + formatValue(value), "");
				sql = sql.replace(" AND " + key + " > " + formatValue(value), "");
				sql = sql.replace(" AND " + key + " < " + formatValue(value), "");
				sql = sql.replace(" AND " + key + " >= " + formatValue(value), "");
				sql = sql.replace(" AND " + key + " <= " + formatValue(value), "");
				sql = sql.replace(" AND " + key + " IN (" + value + ")", "");
				sql = sql.replace(" AND " + key + " NOT IN (" + value + ")", "");
				sql = sql.replace(" AND " + key + " BETWEEN " + formatValue(value), "");
				sql = sql.replace(" AND " + key + " IS NULL", "");
				sql = sql.replace(" AND " + key + " IS NOT NULL", "");
				sql = sql.replace(" AND " + key + " NOT LIKE '%" + value + "%'", "");
			}
		}
		return sql;
	}

	/**
	 * 格式化值为 SQL 兼容的字符串
	 *
	 * @param value 值
	 * @return 格式化后的字符串
	 */
	private String formatValue(Object value) {
		if (value instanceof String) {
			return "'" + value.toString() + "'";
		} else {
			return value.toString();
		}
	}

}
